﻿using B.S.BaseData.ErrorCode;
using B.S.BaseData.Manage.Api.With.Apliaction.Command.ReportlossesModelCommand;
using DAL;
using MediatR;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace B.S.BaseData.Manage.Api.With.Apliaction.CommandHandler.ReportlossesModelCommandHandler
{
    /// <summary>
    /// 报损新增命令处理程序
    /// </summary>
    public class ReportlossesAddModelCommandHandler : IRequestHandler<ReportlossesAddModelCommand, ApiReqult<object>>
    {
        private readonly DBHelper _dbHelper;
        // 每批处理的数据数量
        private const int BatchSize = 100;

        public ReportlossesAddModelCommandHandler(DBHelper dbHelper)
        {
            _dbHelper = dbHelper;
        }

        /// <summary>
        /// 处理报损新增命令
        /// </summary>
        /// <param name="request">报损新增命令请求</param>
        /// <param name="cancellationToken">取消令牌</param>
        /// <returns>操作结果</returns>
        public async Task<ApiReqult<object>> Handle(ReportlossesAddModelCommand request, CancellationToken cancellationToken)
        {
            var result = new ApiReqult<object>();

            try
            {
                int totalRows = 0;
                if (request != null &&
                    request.Lossreportnumber != null && request.Lossreportnumber.Count > 0)
                {
                    totalRows = await InsertRecordsInBatchesAsync(request);
                }

                result.Code = totalRows > 0 ? ApiEnum.添加成功 : ApiEnum.添加失败;
                result.Msg = result.Code == ApiEnum.添加成功 ? "入库数据添加成功" : "入库数据添加失败";
            }
            catch (SqlException ex)
            {
                result.Code = ApiEnum.添加失败;
                result.Msg = $"数据库操作失败：{ex.Message}";
            }
            catch (Exception ex)
            {
                result.Code = ApiEnum.操作失败;
                result.Msg = $"系统异常：{ex.Message}";
            }

            return result;
        }

        /// <summary>
        /// 按批次插入报损记录
        /// </summary>
        /// <param name="request">报损新增命令请求</param>
        /// <returns>插入的总行数</returns>
        private async Task<int> InsertRecordsInBatchesAsync(ReportlossesAddModelCommand request)
        {
            int totalRowsInserted = 0;
            int totalCount = request.Lossreportnumber.Count;

            for (int startIndex = 0; startIndex < totalCount; startIndex += BatchSize)
            {
                int endIndex = Math.Min(startIndex + BatchSize, totalCount);

                // 拆分成单独的行，方便设置断点
                Task<int> insertBatchTask = InsertBatchAsync(request, startIndex, endIndex);
                int rowsInserted = await insertBatchTask;

                totalRowsInserted += rowsInserted;
            }

            return totalRowsInserted;
        }

        /// <summary>
        /// 插入一批报损记录
        /// </summary>
        /// <param name="request">报损新增命令请求</param>
        /// <param name="startIndex">起始索引</param>
        /// <param name="endIndex">结束索引</param>
        /// <returns>插入的行数</returns>
        private async Task<int> InsertBatchAsync(ReportlossesAddModelCommand request, int startIndex, int endIndex)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            // 构建 SQL 插入语句的开头部分
            string insertStatementStart = @"
            INSERT INTO ReportlossesModel (
                Lossreportnumber, Thetypeoflossreport, Associatetheordernumber, Makeasingle, Thetimewhentheorderwasmade,
                Remark, Productname, Productcode, Specification, Batch, Unitprice, Reportedlossquantity,
                Warehouse, Thecurrentlocation, Reviewstatus, Reviewers, Reasonforreview, Reviewtime, IsDelete
            ) VALUES ";
            sqlBuilder.AppendLine(insertStatementStart);

            for (int i = startIndex; i < endIndex; i++)
            {
                int relativeIndex = i - startIndex;

                // 处理多个值的插入
                if (relativeIndex > 0)
                {
                    string comma = ",";
                    sqlBuilder.Append(comma);
                }

                // 构建每一行的插入值
                string lossReportNumberValue = SanitizeValue(request.Lossreportnumber[i]);
                string typeOfLossReportValue = SanitizeValue(request.Thetypeoflossreport != null ? request.Thetypeoflossreport[i] : null);
                string associatedOrderNumberValue = SanitizeValue(request.Associatetheordernumber != null ? request.Associatetheordernumber[i] : null);
                string makeSingleValue = SanitizeValue(request.Makeasingle != null ? request.Makeasingle[i] : null);
                string orderMadeTimeValue = SanitizeValue(request.Thetimewhentheorderwasmade != null ? request.Thetimewhentheorderwasmade[i] : (DateTime?)null);
                string remarkValue = SanitizeValue(request.Remark != null ? request.Remark[i] : null);
                string productNameValue = SanitizeValue(request.Productname != null ? request.Productname[i] : null);
                string productCodeValue = SanitizeValue(request.Productcode != null ? request.Productcode[i] : null);
                string specificationValue = SanitizeValue(request.Specification != null ? request.Specification[i] : null);
                string batchValue = SanitizeValue(request.Batch != null ? request.Batch[i] : null);
                string unitPriceValue = SanitizeValue(request.Unitprice != null ? request.Unitprice[i] : null);
                string reportedLossQuantityValue = SanitizeValue(request.Reportedlossquantity != null ? request.Reportedlossquantity[i] : null);
                string warehouseValue = SanitizeValue(request.Warehouse != null ? request.Warehouse[i] : null);
                string currentLocationValue = SanitizeValue(request.Thecurrentlocation != null ? request.Thecurrentlocation[i] : null);
                string reviewStatusValue = SanitizeValue(request.Reviewstatus != null ? request.Reviewstatus[i] : (int?)null);
                string reviewersValue = SanitizeValue(request.Reviewers != null ? request.Reviewers[i] : null);
                string reviewReasonValue = SanitizeValue(request.Reasonforreview != null ? request.Reasonforreview[i] : null);
                string reviewTimeValue = SanitizeValue(request.Reviewtime != null ? request.Reviewtime[i] : (DateTime?)null);
                string isDeleteValue = SanitizeValue(request.IsDelete != null ? request.IsDelete[i] : 0);

                string rowInsertValue = $@"
                ('{lossReportNumberValue}', '{typeOfLossReportValue}', '{associatedOrderNumberValue}', '{makeSingleValue}', '{orderMadeTimeValue:yyyy-MM-dd HH:mm:ss}',
                '{remarkValue}', '{productNameValue}', '{productCodeValue}', '{specificationValue}', '{batchValue}', '{unitPriceValue}', '{reportedLossQuantityValue}',
                '{warehouseValue}', '{currentLocationValue}', {reviewStatusValue}, '{reviewersValue}', '{reviewReasonValue}', '{reviewTimeValue:yyyy-MM-dd HH:mm:ss}', {isDeleteValue})";

                sqlBuilder.AppendLine(rowInsertValue);
            }

            string sql = sqlBuilder.ToString();

            // 执行非查询操作
            int rowsAffected = _dbHelper.NonQuery(sql);
            return rowsAffected;
        }

        /// <summary>
        /// 对值进行清理，防止 SQL 注入
        /// </summary>
        /// <param name="value">待清理的值</param>
        /// <returns>清理后的值</returns>
        private string SanitizeValue(object value)
        {
            if (value == null)
            {
                return "NULL";
            }
            string strValue = value.ToString();
            // 简单的转义单引号，防止 SQL 注入
            string sanitizedValue = strValue.Replace("'", "''");
            return sanitizedValue;
        }
    }
}